package cn.hpclub.server.service;

import java.util.List;
import java.util.Map.Entry;
import java.util.concurrent.ConcurrentHashMap;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.hpclub.server.constant.Constant;
import cn.hpclub.server.model.ServiceReservation;
import cn.hpclub.server.util.DateKit;
import cn.hpclub.server.util.InterfaceUtil;

public class BroadbandorderService{
    private static final Logger         logger        = LoggerFactory.getLogger(BroadbandorderService.class);
    public static BroadbandorderService service       = new BroadbandorderService();
    public static final String[]        headesExport  = { "称呼", "服务类型", "客户号码", "客户姓名", "所在城市", "所在区县", "预约地址", "预约日期",
            "预约时间", "预约状态", "订单号", "预约名称", "预约简介", "销售员姓名", "客户经理", "销售员电话号码", "店铺名称", "店铺所在城市", "店铺所在区县", "店铺上层机构",
            "发布人机构名称", "其他联系电话", "安装小区", "成员手机号1", "成员手机号2", "成员手机号3", "期望安装时间", "预约外呼时间", "反馈内容", "资费代码", "渠道编码",
            "渠道类型", "店员工号", "店员身份", "办理来源", "备注" };
    public static final String[]        columnsExport = { "gender", "service_type", "telephone", "customer_name",
            "city", "area", "install_address", "reservation_date", "reservation_time", "reservation_state", "boss_no",
            "bd_name", "summary", "member_name", "is_accounts_manager", "member_mobile", "store_name", "store_city",
            "store_area", "store_praent_org", "publish_org_name", "other_contact_phone", "install_area", "phone1",
            "phone2", "phone3", "expected_install_time", "call_time", "feed_back", "business_code", "num_code",
            "org_type", "emp_code", "memberType", "saletype", "member_import_desc" };

    /**
     * 获取查询语句
     */
    public String getSqlExceptSelect(ConcurrentHashMap<String, String> searchInfo){
        int topId = InterfaceUtil.getIdTop();
        String sqlExceptSelect = " from service_reservation sr LEFT JOIN member_org mo ON sr.user_id=mo.id  "
                + " LEFT JOIN (SELECT id AS orgIDTmp FROM `organization` where (parentid=" + topId
                + " OR parentid IN (select id FROM organization WHERE  parentid=" + topId + "))) AS orgType "
                + " ON sr.org_id = orgType.orgIDTmp "
                + " LEFT JOIN (SELECT org.id AS orgIDTmp2, orgcls.WebsiteId AS WebsiteIdType FROM organization org, organization_class orgcls WHERE org.WebsiteId = orgcls.id AND org.enabled=1 AND orgcls.enabled=1 AND ( orgcls.WebsiteId LIKE '6%' OR orgcls.WebsiteId = '1000' OR orgcls.WebsiteId = '4800')) AS orgType2 ON sr.org_id = orgType2.orgIDTmp2"
                + " where sr.service_type=2 and sr.appid='100000056' and sr.is_deleted=0";

        for(Entry<String, String> item : searchInfo.entrySet()){
            sqlExceptSelect += buildQuery(item);
        }
        sqlExceptSelect += " order by sr.create_date desc";

        logger.debug("sqlExceptSelect = " + sqlExceptSelect);
        return sqlExceptSelect;
    }

    private String buildQuery(Entry<String, String> item){
        String sql = "";
        if(item.getKey().equals("telephone")){
            sql += " and sr.telephone=" + item.getValue();
        } else if(item.getKey().equals("gender")){
            sql += " and sr.gender=" + item.getValue();
        } else if(item.getKey().equals("city")){
            sql += " and sr.city='" + item.getValue() + "'";
        } else if(item.getKey().equals("org_id")){
            sql += " and sr.org_id in "
                    + InterfaceUtil.getChlidOrgIdSql(Integer.parseInt(item.getValue()), Constant.MAX_ORG_LEVEL, true);
        } else if(item.getKey().equals("create_date")){
            String date = item.getValue();
            String start = "";
            String end = "";
            if(StrKit.notBlank(date)){
                start = date.split("\\|")[0];
                end = date.split("\\|")[1];
            }
            logger.debug("startDate =" + start);
            logger.debug("endDate =" + end);
            String dateFormate = "%Y-%m-%d";
            sql += " and date_format(create_date, '" + dateFormate + "') >= date_format('" + start + "', '"
                    + dateFormate + "') and date_format(create_date, '" + dateFormate + "') <= date_format('" + end
                    + "', '" + dateFormate + "')";
        } else if(item.getKey().startsWith("isMgr")){
            if("1".equals(item.getValue())){
                sql += " and ((orgType.orgIDTmp is not null) or (orgType2.orgIDTmp2 is not null)) ";
            } else if("0".equals(item.getValue())){
                sql += " and ((orgType.orgIDTmp is  null) and (orgType2.orgIDTmp2 is null)) ";
            }

        }else if(item.getKey().startsWith("memberType")){
            if(!"0".equals(item.getValue())) {
                sql += " and mo.member_type =" + item.getValue();
            }
        }
        return sql;
    }

    /**
     * @param pageNumber
     * @param pageSize
     * @param searchInfo
     * @return
     */
    public Page<ServiceReservation> findByPageBroadbandReservation(int pageNumber, int pageSize,
            ConcurrentHashMap<String, String> searchInfo){
        String select = "select sr.id as id, customer_name, sr.city, area, install_address, bd.`name`, (case gender when 0 then '先生' when 1 then '女士' end) as gender,(case service_type when 2 then '宽带预约' end) as service_type, telephone, (case reservation_state when 0 then '默认' when 1 then '等待审核' when 2 then '审核通过' when 3 then '审核不通过' when 4 then '结单' end) as reservation_state, create_date, end_date";
        String sqlExceptSelect = getSqlExceptSelect(searchInfo);

        return ServiceReservation.dao.paginate(pageNumber, pageSize, select, sqlExceptSelect);
    }

    public String getSqlSelect(){
        String dateFrm = "%Y/%m/%d %H:%i:%s";
        String select = " select mo.emp_code AS emp_code, sr.id as id, (case sr.sale_type when 1 then 'APP' when 2 then '分享页面' when 3 then '好友圈' else '未知' end) as saletype,customer_name, sr.city, area,install_address, other_contact_phone, install_area, member_phone,date_format(expected_install_time, '"
                + dateFrm + "') as expected_install_time, date_format(date_add(create_date, interval 2 day), '"
                + dateFrm + "' ) as call_time, feed_back,bd_name,bd_code as business_code,(case member_type when 1 then '店长' when 2 then '店员' when 3 then '泛店员' else '未知' end) as memberType,"
                + " (case gender when 0 then '先生' when 1 then '女士' end) as gender,(case service_type when 2 then '宽带预约' end) as service_type, "
                + " telephone, (case reservation_state when 0 then '默认' when 1 then '等待审核' when 2 then '审核通过' when 3 then '审核不通过' when 4 then '结单' end) as reservation_state, "
                + " date_format(create_date, '%Y-%m-%d %H:%i:%s') as create_date,end_date, sr.boss_no, bd_name as name, bd_summary as summary, "
                + " (select name from member_org m_org where m_org.enabled = 1 and sr.user_id = m_org.id) as member_name, "
                + " (select mobile from member_org m_org where m_org.enabled = 1 and sr.user_id = m_org.id) as member_mobile, "
                + " (select DisplayName from organization where is_deleted = 0 and id = sr.org_id) as store_name, "
                + " (select class_name from organization org, organization_class orgClass where org.WebsiteId=orgClass.id and org.is_deleted = 0 and org.id = sr.org_id) as org_type, "
                + InterfaceUtil.getCitySql("sr.org_id") + " as store_city,"// city
                + InterfaceUtil.getAreaSql("sr.org_id") + " as store_area,"// area
                + " (select (select DisplayName from organization as prt_org where is_deleted = 0 and prt_org.id = sub_org.parentid) "
                + " from organization as sub_org where is_deleted = 0 and id = sr.org_id) as store_praent_org, "
                + " bd_creator_org_name as publish_org_name, "
                + " (select org.NumCode from organization org where org.id=sr.org_id) as num_code,"
                + " (select import_desc from member_org m_org where m_org.enabled = 1 and sr.user_id = m_org.id) as member_import_desc,"
                + " IF(orgType.orgIDTmp || orgType2.orgIDTmp2 , '是', '否') AS is_accounts_manager";

        // System.out.println("select:" + select);
        return select;
    }

    /**
     * @param searchInfo
     * @Title: getListExport
     * @Description:TODO
     * @return List<Record>
     */
    public List<Record> getListExport(ConcurrentHashMap<String, String> searchInfo){
        String select = getSqlSelect();

        String sqlExceptSelect = getSqlExceptSelect(searchInfo);

        List<Record> list = Db.find(select + sqlExceptSelect);

        for(Record r : list){
            // 解析成员手机号
            String memberPhone = r.getStr("member_phone");
            if(StrKit.notBlank(memberPhone)){
                String[] phoneArr = memberPhone.split(",");
                for(int i = 1; i <= phoneArr.length; i++){
                    r.set("phone" + i, phoneArr[i - 1]);
                }
            }

            // 拆分预约日期
            String createDate = r.getStr("create_date");
            if(createDate != null){
                r.set("reservation_date",
                      DateKit.toStr(DateKit.toDate(createDate, "yyyy-MM-dd HH:mm:ss"), "yyyy-MM-dd"));
                r.set("reservation_time", DateKit.toStr(DateKit.toDate(createDate, "yyyy-MM-dd HH:mm:ss"), "HH:mm:ss"));
            }
        }

        return list;
    }

    /**
     * @Title: getListSqlSelect
     * @Description:TODO
     * @return String
     */
    public String getListSqlSelect(){
        String dateFrm = "%Y-%m-%d %H:%i:%s";
        String select = "select mo.emp_code as emp_code,sr.id as id, customer_name, (case sr.sale_type when 1 then 'APP' when 2 then '分享页面' when 3 then '好友圈' else '未知' end) as saletype,sr.city, area, install_address, other_contact_phone, install_area, date_format(expected_install_time, '"
                + dateFrm + "') as expected_install_time, date_format( date_add(create_date, interval 2 day), '"
                + dateFrm
                + "' ) as call_time, member_phone,(case member_type when 1 then '店长' when 2 then '店员' when 3 then '泛店员' else '未知' end) as memberType, feed_back, bd_name as name, bd_code as business_code,  (select org.NumCode from organization org where org.id=sr.org_id) as num_code, (select class_name from organization org, organization_class orgClass where org.WebsiteId=orgClass.id and org.is_deleted = 0 and org.id = sr.org_id) as org_type,  (case gender when 0 then '先生' when 1 then '女士' end) as gender,(case service_type when 2 then '宽带预约' end) as service_type, telephone, (case reservation_state when 0 then '默认' when 1 then '等待审核' when 2 then '审核通过' when 3 then '审核不通过' when 4 then '结单' end) as reservation_state, create_date, end_date,IF(orgType.orgIDTmp || orgType2.orgIDTmp2 , '是', '否') AS is_accounts_manager";
        // System.out.println("select:" + select);
        return select;
    }
}
